package src.反射7;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.beanutils.BeanUtils;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;
/*Date:2025/3/18
                                 连接数据库的一些方法
 * api:
    getConnection（）:德鲁伊数据池连接
    Preset():预编译
                  return PreparedStatement
    update（）： 删除，插入，更新数据
                  return Integer
    findList（）：查询数据

 *   queryBean():  查询表的所有数据  1.sql：返回sql语句，类似select * from stu where ？
 *                          2.反射的可变参数
 *                          3.可变参数
 *                  return T
     queryBeanList():将查询的数据储存到集合中
                    return <T> List<T>
 *
 *   queryMap（）：获取表中某一条记录，以hashcode形式（键值对）存储返回
 *                         1.sql：返回sql语句，类似select * from stu where ？
 *                         2.可变参数
 *                  return   Map<String,Object>

     queryList（）：把多个hashmap对象封装到list集合中
     *                     1.sql：返回sql语句，类似select * from stu where ？
 *                         2.可变参数
 *                   return ArrayList<Map>

     queryArray(): 获取表中的一条数据，以集合的形式返回
                            1.sql：返回sql语句，类似select * from stu where ？
 *                         2.可变参数
 *                    return Object[]
 *
 *   queryArrayList(): 把多个数组对象以集合的形式返回
 *                         1.sql：返回sql语句，类似select * from stu where ？
 *                         2.可变参数
 *                    return  ArrayList<Object[]>

      close():关闭资源
      *                    1.connection：连接数据库
      *                    2.resultSet：执行sql语句
      *                    3.statement：预编译对象
 */
    public class MyhandleDbReflection {

        /**
         * 1、获取数据库服务器的连接
         * @return 连接对象
         */
        public static Connection getConnection() throws SQLException {

            Connection connection = null;
            try {
                Properties properties = new Properties();
                FileInputStream fileInputStream = new FileInputStream("src/main/resources/DruidJdbc.properties");
                properties.load(fileInputStream);
//使用德鲁伊数据库连接池技术
                DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

                connection = dataSource.getConnection();
                System.out.println("connection = " + connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return connection;
        }

        /**
         * 2.获取sql语句的预编译对象
         * @param connection 服务器连接对象
         * @param sql 执行的sql语句 类似 select * from stu where name=?
         * @param parameters sql中替换?的参数
         * @return PreparedStatement 预编译对象
         */
        public static PreparedStatement PreSta(Connection connection, String sql, Object... parameters) throws SQLException {
            PreparedStatement statement = null;
                statement = connection.prepareStatement(sql);
//获取徐字符串中参数个数
                ParameterMetaData parameterMetaData = statement.getParameterMetaData();
                System.out.println("parameterMetaData = " + parameterMetaData);
                int num = parameterMetaData.getParameterCount();
                System.out.println("num = " + num);
                if (num != 0) {
                    for (int i = 0; i < num; i++) {
                        statement.setObject(i + 1, parameters[i]);
                    }
                }
            return statement;
        }

        public static int update(String sql, Object... parameters) throws SQLException {
            int f = 0;
                Connection connection = MyhandleDbReflection.getConnection();

                PreparedStatement statement = MyhandleDbReflection.PreSta(connection, sql, parameters);

                f = statement.executeUpdate();
            return f;
        }

        public static <T> List<T> findList(String sql, Class<T> c) throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException {
//创建集合，存储所有对象数据
            ArrayList<T> arrayList = new ArrayList<>();
                Connection conn = getConnection();
//创建statement
                PreparedStatement statement = conn.prepareStatement(sql);
//返回结果集合
                ResultSet resultSet = statement.executeQuery();
                System.out.println("resultSet = " + resultSet);
//从结果集合中获取结果集的元数据
                ResultSetMetaData metaData = resultSet.getMetaData();
                System.out.println("metaData = " + metaData);

//获取集合中列数
                int count = metaData.getColumnCount();
                System.out.println("count = " + count);
//解析结果集合
                while (resultSet.next()) {
//动态获取一行数据之后，封装成实体对象
                    T t = c.newInstance();
                    System.out.println("t = " + t);
                    for (int i = 1; i <= count; i++) {
//通过列的序号获取列值
                        Object value = resultSet.getObject(i);
                        if (value != null) {
//通过列的序号获取列名
                            String columnName = metaData.getColumnName(i);
                            System.out.println("columnName = " + columnName);
                            Field declaredField = c.getDeclaredField(columnName);
                            declaredField.setAccessible(true);//允许访问javabean私有属性
                            declaredField.set(t, value);
                        }
                    }
                    arrayList.add(t);
                }
                MyhandleDbReflection.close(conn, resultSet, statement);
            return arrayList;
            }
        /**
         * 删除，更新 查询一条记录封装到一个javaBean对象中
         * select * from stu where id=?
         *
        */
//select * from stu where id=?, User.class,
        public static <T> T queryBean(String sql, Class<T> cls, Object... parameters) throws SQLException, NoSuchMethodException, InvocationTargetException, IllegalAccessException, InstantiationException {
//1.定义需要的对象
            Connection connection = null;
            PreparedStatement statement = null;
            ResultSet resultSet = null;

            T t = null;
//2.连接数据库
                connection = MyhandleDbReflection.getConnection();
                statement = PreSta(connection, sql, parameters);
                resultSet = statement.executeQuery();
                ResultSetMetaData metaData = resultSet.getMetaData();
// System.out.println("测试元数据metaData = " + metaData);
                while (resultSet.next()) {
                    t = cls.getConstructor().newInstance();
//获取这一行记录字段的个数
                    int count = metaData.getColumnCount();
                    for (int i = 1; i <= count; i++) {
//表中的记录映射到javabean实体类的每一个属性上
                        BeanUtils.setProperty(t,
                                metaData.getColumnName(i), resultSet.getObject(i));
                    }
                }
                MyhandleDbReflection.close(connection, resultSet, statement);

            return t;
        }

        public static <T> List<T> queryBeanList(String sql, Class<T> cls, Object... parameters) throws SQLException, NoSuchMethodException, InvocationTargetException, IllegalAccessException, InstantiationException {
            Connection connection = null;
            PreparedStatement statement = null;
            ResultSet resultSet = null;
            ArrayList<T> arrayList = new ArrayList<>();
                connection = MyhandleDbReflection.getConnection();
                statement = PreSta(connection, sql, parameters);
                resultSet = statement.executeQuery();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int count = metaData.getColumnCount();
                while (resultSet.next()) {
                    T t = cls.getConstructor().newInstance();
                    for (int i = 1; i <= count; i++) {
                        BeanUtils.setProperty(t,
                                metaData.getColumnName(i),
                                resultSet.getObject(i));
                    }
                    arrayList.add(t);
                }
                MyhandleDbReflection.close(connection, resultSet,statement);
            return arrayList;
        }

        /**
         * 1、获取表中某一条记录，以hashMap形式(键值对)存储返回
         */
        public static Map<String, Object> queryMap(String sql, Object... parameters) throws SQLException {
            HashMap<String, Object> stringObjectHashMap = null;
                Connection connection = MyhandleDbReflection.getConnection();
                PreparedStatement prestatement = PreSta(connection, sql, parameters);
                ResultSet resultSet = prestatement.executeQuery();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int count = metaData.getColumnCount();
                stringObjectHashMap = new HashMap<>();
// while(resultSet.next())
// {
                resultSet.next();
                for (int i = 0; i < count; i++) {
                    stringObjectHashMap.put(metaData.getColumnName(i + 1), resultSet.getObject(i + 1));
                }
                MyhandleDbReflection.close(connection, resultSet, prestatement);
            return stringObjectHashMap;
        }

        /**
         * 2.
         */
        public static ArrayList<Map> queryMapList(String sql, Object... parameters) throws SQLException {


            ArrayList<Map> maps = null;
                Connection connection = MyhandleDbReflection.getConnection();
                PreparedStatement prestatement = PreSta(connection, sql, parameters);
                ResultSet resultSet = prestatement.executeQuery();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int count = metaData.getColumnCount();
                maps = new ArrayList<>();
                while (resultSet.next()) {
                    HashMap<String, Object> stringObjectHashMap = new HashMap<>();
                    for (int i = 1; i <= count; i++) {
                        stringObjectHashMap.put(metaData.getColumnName(i), resultSet.getObject(i));
                    }
                    maps.add(stringObjectHashMap);
                }
                MyhandleDbReflection.close(connection, resultSet, prestatement);
            return maps;
        }

        /**
         * 3
         */
        public static Object[] queryArray(String sql, Object... parameters) {
            Object[] objects = new Object[0];
            try {
                Connection connection = MyhandleDbReflection.getConnection();
                PreparedStatement statement = PreSta(connection, sql, parameters);
                ResultSet resultSet = statement.executeQuery();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int count = metaData.getColumnCount();
                objects = new Object[count];
                resultSet.next();
                for (int i = 0; i < count; i++) {
                    objects[i] = resultSet.getObject(i + 1);
                }
                MyhandleDbReflection.close(connection, resultSet, statement);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return objects;
        }

        /**
         * 把多个数组对象以集合的存储形式返回
         */

        public static ArrayList<Object[]> queryArrayList(String sql, Object... parameters) {


            ArrayList<Object[]> objects = null;
            try {
                Connection connection = MyhandleDbReflection.getConnection();
                PreparedStatement statement = PreSta(connection, sql, parameters);
                ResultSet resultSet = statement.executeQuery();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int count = metaData.getColumnCount();
                objects = new ArrayList<>();
                while (resultSet.next()) {
                    Object[] object = new Object[count];
                    for (int i = 0; i < count; i++) {
                        object[i] = resultSet.getObject(i + 1);
                    }
                    objects.add(object);
                }
                MyhandleDbReflection.close(connection, resultSet, statement);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

            return objects;
        }
        public static void close(Connection conn, ResultSet resultSet, PreparedStatement preparedStatement) {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }